import requests
import json
import pymysql
import pprint

data = []
def Covd_19():
    url = "https://view.inews.qq.com/g2/getOnsInfo?name=disease_h5"
    headrs = {
        "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.149 Safari/537.36"}

    r = requests.get(url,headrs)
    res = r.json()
    data_all = json.loads(res["data"])
    Province = data_all["areaTree"][0]["children"]
    china_total = "现在有确诊" + str(data_all["chinaTotal"]["nowConfirm"]) +\
                  "疑似" + str(data_all["chinaTotal"]["suspect"]) + \
                  "死亡" + str(data_all["chinaTotal"]["dead"]) + \
                  "治愈" + str(data_all["chinaTotal"]["heal"]) + \
                  "更新时间" + str(data_all["lastUpdateTime"]) + "中国加油"
    for i in range(len(Province)):
        data.append([Province[i]["name"],Province[i]["total"]["nowConfirm"],Province[i]["total"]["confirm"],
                     Province[i]["total"]["suspect"], Province[i]["total"]["dead"], Province[i]["total"]["heal"]])
    pprint.pprint((data))
def mysql():
    print("---------开始数据存取---------")
    connection = pymysql.connect(host = "127.0.0.1", port = 3306, user = "root", passwd = "root", db = "covdb", charset = "utf8")
    connent = connection.cursor()
    print("连接成功")
    sql = "insert into covd(province,nowconfirm,confirm,suspect,dead,heal) values (%s, %s, %s, %s ,%s, %s)"
    try:
        print("开始插入")
        for a in range(len(data)):
            connent.execute(sql, (data[a]))
        connection.commit()
        print("插入成功")
    except Exception as e:
        print(e)
        connection.rollback()
        print("插入失败")
    finally:
        connent.close()
        connection.close()
Covd_19()
mysql()